package co.gov.medellin.ssti.sub.paoyer.administration.dao;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.WarehouseByBranchVO;

/**
 * DAO para consultar registros en la vista VFD_BODEGAS_X_SURCURSAL
 * 
 * @author Rene.Rangel@consultsoft.com
 * @version 1.0
 */

public class WarehouseByBranchDAO {
	/**
	 * Conexion a la base de datos.
	 */
	private Connection conn = null;

	/**
	 * Constructor que inicializa la conexion con la base de datos.
	 * 
	 * @param conn
	 *            Conexion con la base de datos.
	 */

	public WarehouseByBranchDAO(Connection conn) {
		this.conn = conn;
	}

	/**
	 * Consulta un grupo de registros de la vista VFD_BODEGAS_X_SUCURSAL a
	 * partir de las condiciones que vienen en el Value Object
	 * 
	 * @param WarehouseByBranchVO
	 *            Value Object con las condiciones a tener encuenta.
	 * 
	 * @return Collection Arreglo de registros retornados de la consulta.
	 */
	public Collection<WarehouseByBranchVO> findWarehouseBranchByCriterea(
			WarehouseByBranchVO criterea) throws SQLException {

		/**
		 * Se consulta sobre la vista creada para este fin
		 */

		String query = "SELECT bosu.* " + "FROM vfd_bodegas_x_sucursal bosu ";

		String filters = "";
		// Filtro por Nombre de Sucursal
		if ((criterea.getBranchName() != null)
				&& !(criterea.getBranchName().equals(""))) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " UPPER(bosu.sucu_nombre) LIKE '%"
						+ criterea.getBranchName().toUpperCase() + "%' ";
			} else {
				filters = filters + " AND UPPER(bosu.sucu_nombre) LIKE '%"
						+ criterea.getBranchName().toUpperCase() + "%' ";
			}
		}

		// Filtro por Codigo Usuario de Sucursal
		if ((criterea.getBranchCustomCode() != null)
				&& !(criterea.getBranchCustomCode().intValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " bosu.sucu_codusu = "
						+ criterea.getBranchCustomCode();
			} else {
				filters = filters + " AND bosu.sucu_codusu = "
						+ criterea.getBranchCustomCode();
			}
		}

		// Filtro por Codigo interno de Sucursal
		if ((criterea.getBranchInternalCode() != null)
				&& !(criterea.getBranchInternalCode().intValue() == 0)) {
			if (filters.equals("")) {
				filters = filters + " bosu.sucu_codin = "
						+ criterea.getBranchInternalCode();
			} else {
				filters = filters + " AND bosu.sucu_codin = "
						+ criterea.getBranchInternalCode();
			}
		}

		// Filtro por Nombre de Bodega
		if ((criterea.getWarehouseName() != null)
				&& !(criterea.getWarehouseName().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " bosu.bode_nombre LIKE '%"
						+ criterea.getWarehouseName().toUpperCase() + "%'";
			} else {
				filters = filters + " AND bosu.bode_nombre LIKE '%"
						+ criterea.getWarehouseName().toUpperCase() + "%'";
			}
		}

		// Filtro por Bodega Activa
		if ((criterea.getActiveWarehouse() != null)
				&& !(criterea.getActiveWarehouse().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " bosu.bode_activo = '"
						+ criterea.getActiveWarehouse() + "'";
			} else {
				filters = filters + " AND bosu.bode_activo = '"
						+ criterea.getActiveWarehouse() + "'";
			}
		}

		// Filtro por Codigo Interno de Bodega
		if ((criterea.getWarehouseInternalCode() != null)
				&& !(criterea.getWarehouseInternalCode().intValue() == 0)) {
			if (filters.equals("")) {
				filters = filters + " bosu.bode_codin = "
						+ criterea.getWarehouseInternalCode();
			} else {
				filters = filters + " AND bosu.bode_codin = "
						+ criterea.getWarehouseInternalCode();
			}
		}

		// Filtro por Codigo Interno de Clase de Bodega
		if ((criterea.getWarehouseClassCode() != null)
				&& !(criterea.getWarehouseClassCode().intValue() == 0)) {
			if (filters.equals("")) {
				filters = filters + " bosu.clbo_codigo = "
						+ criterea.getWarehouseClassCode();
			} else {
				filters = filters + " AND bosu.clbo_codigo = "
						+ criterea.getWarehouseClassCode();
			}
		}

		// Filtro por Descripcion de Clase de Bodega
		if ((criterea.getWarehouseClassDescription() != null)
				&& !(criterea.getWarehouseClassDescription().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " bosu.clbo_descripcion = '"
						+ criterea.getWarehouseClassDescription() + "'";
			} else {
				filters = filters + " AND bosu.clbo_descripcion = '"
						+ criterea.getWarehouseClassDescription() + "'";
			}
		}

		// Filtro por Clase de Bodega Activa
		if ((criterea.getActiveWarehouseClass() != null)
				&& !(criterea.getActiveWarehouseClass().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " bosu.clbo_activo = '"
						+ criterea.getActiveWarehouseClass() + "'";
			} else {
				filters = filters + " AND bosu.clbo_activo = '"
						+ criterea.getActiveWarehouseClass() + "'";
			}
		}

		// Filtro por Sigla de Clase de Bodega
		if ((criterea.getWarehouseClassAbbreviation() != null)
				&& !(criterea.getWarehouseClassAbbreviation().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " bosu.clbo_sigla = '"
						+ criterea.getWarehouseClassAbbreviation() + "'";
			} else {
				filters = filters + " AND bosu.clbo_sigla = '"
						+ criterea.getWarehouseClassAbbreviation() + "'";
			}
		}

		// Si hay filtros, entonces armar la clausula WHERE
		if (!filters.equals("")) {
			query = query + " WHERE " + filters;
		}

		Statement stm = null;
		ResultSet rst = null;
		ArrayList<WarehouseByBranchVO> warehouseBranchs = new ArrayList<WarehouseByBranchVO>();

		stm = conn.createStatement();
		rst = stm.executeQuery(query);

		while (rst.next()) {
			WarehouseByBranchVO warehouseBranch = new WarehouseByBranchVO();
			warehouseBranch.setBranchInternalCode(new Long(rst
					.getLong("sucu_codin")));
			warehouseBranch.setBranchCustomCode(new Short(rst
					.getShort("sucu_codusu")));
			warehouseBranch.setBranchName(rst.getString("sucu_nombre"));
			warehouseBranch.setBranchAddress(rst.getString("sucu_direccion"));
			warehouseBranch
					.setBranchPhoneNumber(rst.getString("sucu_telefono"));
			warehouseBranch.setActiveBranch(rst.getString("sucu_activo"));
			warehouseBranch.setBranchFax(rst.getString("sucu_fax"));
			warehouseBranch.setBranchPostOfficeBox(rst
					.getString("sucu_apartado_aereo"));
			warehouseBranch.setBranchCityCode(new Long(rst
					.getLong("sucu_ciu_codigo")));
			warehouseBranch.setBranchManager(rst.getString("sucu_gerente"));
			warehouseBranch.setWarehouseInternalCode(new Long(rst
					.getLong("bode_codin")));
			warehouseBranch.setWarehouseName(rst.getString("bode_nombre"));
			warehouseBranch.setActiveWarehouse(rst.getString("bode_activo"));
			warehouseBranch.setWarehouseClassCode(new Long(rst
					.getLong("clbo_codigo")));
			warehouseBranch.setWarehouseClassDescription(rst
					.getString("clbo_descripcion"));
			warehouseBranch.setActiveWarehouseClass(rst
					.getString("clbo_activo"));
			warehouseBranch.setWarehouseClassAbbreviation(rst
					.getString("clbo_sigla"));
			warehouseBranchs.add(warehouseBranch);
		}

		stm.close();

		return warehouseBranchs;
	}

}